package com.wxy.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.wxy.config.GetConnectionConfig;
import com.wxy.eneity.ColumnMessage;
import com.wxy.eneity.IdConnection;
import com.wxy.service.TableService;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author 47868
 */
@Service("TableService")
public class TableServiceImpl implements TableService {
    private static final Pattern PATTERN = Pattern.compile(".*/([^/?]+)");
    public static void closeConnection(Connection connection) {
        try {
            if(connection!=null && !connection.isClosed()){
                connection.close();
            }
        } catch (Exception e) {
                System.out.println(e.getMessage());
        }
    }

    @Override
    public List<String> getTableNames(IdConnection idConnection) {
        ResultSet rs=null;
        Connection connection=null;
        try {
            connection = GetConnectionConfig.getConnection(BeanUtil.toBean(idConnection,com.wxy.eneity.Connection.class));
            String sql = "show tables";
            rs = connection.prepareStatement(sql).executeQuery();
            List<String> list = new ArrayList<>();
            while (rs.next()) {
                list.add(rs.getString(1));
            }
            return list;
        }
        catch (Exception e){
            System.out.println(e.getMessage());
        }
        finally {
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
            closeConnection(connection);
        }
        return null;
    }

    @Override
    public String addTable(IdConnection idConnection, String sql) {
        Connection connection=null;
        try {
            connection = GetConnectionConfig.getConnection(BeanUtil.toBean(idConnection,com.wxy.eneity.Connection.class));
            connection.prepareStatement(sql).executeUpdate();
            return "创建成功";
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        finally {
            closeConnection(connection);
        }
        return "创建失败";
    }

    @Override
    public String deleteTable(IdConnection idConnection, String tableName) {
        Connection connection=null;
        try {
            connection = GetConnectionConfig.getConnection(BeanUtil.toBean(idConnection,com.wxy.eneity.Connection.class));
            String sql = "drop table "+tableName;
            connection.prepareStatement(sql).executeUpdate();
            return "删除成功";
        }
        catch (Exception e){
            System.out.println(e.getMessage());
        }
        finally {
            closeConnection(connection);
        }
        return "该表与其他表绑定，无法删除...";
    }
    @Override
    public List<ColumnMessage> getColumnMessage(IdConnection idConnection,String tableName) {
        Connection connection=null;
        ResultSet rs=null;
        try {
            //获取connection连接，用于后面查询表结构
            connection = GetConnectionConfig.getConnection(BeanUtil.toBean(idConnection,com.wxy.eneity.Connection.class));
            String databaseName=null;
            //要查询的数据库名称
            Matcher matcher = PATTERN.matcher(idConnection.getUrl());
            if (matcher.find()) {
                databaseName = matcher.group(1);
            }
            //使用JDBC元数据API获取表结构
            DatabaseMetaData metaData = connection.getMetaData();
            //获取结果集
            rs = metaData.getColumns(databaseName,null,tableName,null);
            List<ColumnMessage> columnMessages = new ArrayList<>();
            //遍历
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                String columnType = rs.getString("TYPE_NAME");
                String columnSize = rs.getString("COLUMN_SIZE");
                int nullable = rs.getInt("NULLABLE");
                ColumnMessage columnMessage = new ColumnMessage(columnName,columnType+"("+columnSize+")", nullable == 1);
                columnMessages.add(columnMessage);
            }
            return columnMessages;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            closeConnection(connection);
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
        }
    }

    @Override
    public List<List<Object>> getTableData(IdConnection idConnection, String tableName, int page, int size) {
        Connection connection=null;
        ResultSet rs=null;
        try {
            connection = GetConnectionConfig.getConnection(BeanUtil.toBean(idConnection,com.wxy.eneity.Connection.class));
            String sql = "select * from "+tableName+" limit ?,?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, (page-1) * size);
            preparedStatement.setInt(2, size);
            rs = preparedStatement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<List<Object>> data = new ArrayList<>();
            while (rs.next()) {
                List<Object> one = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    String column = rs.getString(i);
                    one.add(column);
                }
                data.add(one);
            }
            System.out.println(data);
            return data;
        }
        catch (Exception e){
            System.out.println(e.getMessage());
        }
        finally {
            if(rs!=null){
                try {
                    rs.close();
                }
                catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
            closeConnection(connection);
        }
        return null;
    }
}
